package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Array;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import com.feib.soeasy.dao.FormatDefinitionDao;
import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.dao.P0028DetailDao;
import com.feib.soeasy.dao.P0028MasterDao;
import com.feib.soeasy.dao.PaymentDao;
import com.feib.soeasy.dao.RealAccountDao;
import com.feib.soeasy.model.FormatDefinition;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.P0028Detail;
import com.feib.soeasy.model.P0028Master;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.RealAccount;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.service.Soez5007Service;
import com.feib.soeasy.util.SoezListBean;
import com.feib.soeasy.util.UserUtil;

/**
 * @title (#)Soez5007ServiceImpl.java<br>
 * @description <br>
 * @author Leo Lee<br>
 * @version 1.0.0 2011/12/07
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/12/07 create by Leo Lee<br>
 */
@Service("soez5007Service")
public class Soez5007ServiceImpl extends BaseServiceImpl implements
		Soez5007Service {
	
    
    @Resource(name="groupDao")
    private GroupDao groupDao;
    
    @Resource(name="realAccountDao")
    private RealAccountDao realAccountDao;

    
    @Resource(name="paymentDao")
    private PaymentDao paymentDao;
    
    @Resource(name="p0028MasterDao")
    private P0028MasterDao p0028MasterDao;
    

    @Resource(name="p0028DetailDao")
    private P0028DetailDao p0028DetailDao;
    
    @Resource(name="formatDefinitionDao")
    private FormatDefinitionDao formatDefinitionDao;
    
    /**
     * 暫存目錄
     */
    @Resource(name = "tempDirectory")
    private String tempDirectory;
    
    @Override
    public List queryDetail(Payment payment, Date qryDateFrom , Date qryDateTo)
    {
    	List details = null;
    	//若繳費帳號14碼則去對USERNO相同的資料。
		//若繳費帳號16碼，則先找16碼的，若沒有取繳費帳號14碼再找一次。
		if(payment.getVirtualAccount().length() == 14)
		{
			details = p0028DetailDao.queryP0028DetailByUserno(payment.getVirtualAccount(),qryDateFrom, qryDateTo);
		}else
		{
			details = p0028DetailDao.queryP0028DetailByUserno(payment.getVirtualAccount().substring(0,16),qryDateFrom, qryDateTo);
			if(details == null || details.size() == 0)
			{
				details = p0028DetailDao.queryP0028DetailByUserno(payment.getVirtualAccount().substring(0,14),qryDateFrom, qryDateTo);
			}
		}
		
		
		
		return details;
    }	
    
    public List queryMasterLists(Payment payment, Date qryDateFrom , Date qryDateTo)
    {
		List<SoezListBean> resultBeans = new ArrayList<SoezListBean>();
		
		RealAccount realAccount = new RealAccount();
		realAccount.setGroup(payment.getGroup());
		List realAccounts = realAccountDao.queryByGroup(realAccount);
		for(Iterator r=realAccounts.iterator();r.hasNext();)
		{
			RealAccount account = (RealAccount)r.next();
			List masters = p0028MasterDao.queryP0028MasterByAday(
						qryDateFrom, qryDateTo,account.getVirtualAccountNo(),payment);
			for(Iterator<P0028Master> i=masters.iterator();i.hasNext();)
			{
				P0028Master master = (P0028Master)i.next();
			
				List paymentResults = null;
				paymentResults = paymentDao.queryPaymentByParam(payment, master.getUserno().trim(), "1");
				if(paymentResults == null || paymentResults.size() == 0)
					paymentResults = paymentDao.queryPaymentByParam(payment, master.getUserno().trim(), "2");
	
				if(paymentResults != null && paymentResults.size() >0)
				{
					Payment p = (Payment)paymentResults.get(0);
					Payment p2 = new Payment();
					p2.setVirtualAccount(master.getUserno());
					p2.setGroup(p.getGroup());
					p2.getGroup().setGroupName(p.getGroup().getGroupName());
					p2.setPayerName(p.getPayerName());
					p2.setPayerId(p.getPayerId());
					p2.setPaymentPk(p.getPaymentPk());
					SoezListBean bean = new SoezListBean();
					bean.setPayment(p2);
					bean.setAvbal(master.getAvbal());
					resultBeans.add(bean);
				}
			}
		}
		
		return resultBeans;
    }
    
    @Override
	public PaginatedList queryResult(Payment payment, Date qryDateFrom , Date qryDateTo , PaginatedList qryResult)
	{
    	
    	List<SoezListBean> resultBeans = queryMasterLists(payment, qryDateFrom, qryDateTo);
    	
		PaginatedList result = new PaginatedList();
		result.setFullListSize(resultBeans.size());
		result.setPageSize(qryResult.getPageSize());
        result.setPageNumber(qryResult.getPageNumber());
        result.setSortCriterion(qryResult.getSortCriterion());
        result.setSortDirection(qryResult.getSortDirection());
        
        
        List<SoezListBean> resultBeans2 = new ArrayList<SoezListBean>();
        for(int i=(qryResult.getPageNumber()-1)*qryResult.getPageSize();i<qryResult.getPageNumber()*qryResult.getPageSize();i++ )
        {
        	if(i == resultBeans.size())
        	{
        		break;
        	}
        	SoezListBean bean2 = (SoezListBean)resultBeans.get(i);
        	resultBeans2.add(bean2);
        }
        
        result.setPaginatedList(resultBeans2);
		return result;
	}
    
    @Override
    public String doExportMasterXls(Payment payment, Date qryDateFrom , Date qryDateTo)
    { 
    	List<SoezListBean> resultBeans = queryMasterLists(payment, qryDateFrom, qryDateTo);

        // 無統計資料
        if (null == resultBeans || 0 == resultBeans.size())
            return null;

        SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
        SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = wb.createSheet("客戶繳款記錄");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setZoom(3, 4);

        // 行數
        int rownum = 0;

        //title row
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints((float)32.25);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("客戶繳款記錄");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));

        Row blankRow = sheet.createRow(rownum++);
        blankRow.setHeightInPoints((float)18);

        //資料期間
        
        Row conditionRow1 = sheet.createRow(rownum++);
        conditionRow1.setHeightInPoints((float)24.5);
        Cell conditionCell = conditionRow1.createCell(0);
        conditionCell.setCellValue("業者代號:" + payment.getGroup().getGroupNo() + "　" + payment.getGroup().getGroupName());
        conditionCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
        
        conditionRow1 = sheet.createRow(rownum++);
        conditionRow1.setHeightInPoints((float)24.5);
        conditionCell = conditionRow1.createCell(0);
        conditionCell.setCellValue("繳款期間:" + dateFormat1.format(qryDateFrom)+ "~" +dateFormat1.format(qryDateTo));
        conditionCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));

        FormatDefinition formatDefinition = formatDefinitionDao.findByPk(payment.getUploadFile().getFormatDefinition().getFormatDefinitionPk());
        conditionRow1 = sheet.createRow(rownum++);
        conditionRow1.setHeightInPoints((float)24.5);
        conditionCell = conditionRow1.createCell(0);
        conditionCell.setCellValue("收款名稱:" + formatDefinition.getFormatName());
        conditionCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));

        if(payment.getPayerName() != null)
        {
        	conditionRow1 = sheet.createRow(rownum++);
            conditionRow1.setHeightInPoints((float)24.5);
            conditionCell = conditionRow1.createCell(0);
            conditionCell.setCellValue("客戶姓名:" + payment.getPayerName());
            conditionCell.setCellStyle(styles.get("title2"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
        }
        
        if(payment.getPayerId() != null)
        {
        	conditionRow1 = sheet.createRow(rownum++);
            conditionRow1.setHeightInPoints((float)24.5);
            conditionCell = conditionRow1.createCell(0);
            conditionCell.setCellValue("客戶編號:" + payment.getPayerId());
            conditionCell.setCellStyle(styles.get("title2"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
        }
        
        if(payment.getVirtualAccount() != null)
        {
        	conditionRow1 = sheet.createRow(rownum++);
            conditionRow1.setHeightInPoints((float)24.5);
            conditionCell = conditionRow1.createCell(0);
            conditionCell.setCellValue("繳款帳號:" + payment.getVirtualAccount());
            conditionCell.setCellStyle(styles.get("title2"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
        }
        
        blankRow = sheet.createRow(rownum++);
        blankRow.setHeightInPoints((float)18);
        
        conditionRow1 = sheet.createRow(rownum++);
        conditionRow1.setHeightInPoints((float)24.5);        

        
        Cell printDateCell = conditionRow1.createCell(0);
        printDateCell.setCellValue("製表日期/時間:" + dateFormat2.format(Calendar.getInstance().getTime()));
        printDateCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));


        //header row



        String[] titles = {"繳費帳號","客戶名稱" ,"客戶編號", "餘額"};


        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints((float) 25.5);
        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }


        long recordCountStoreTotal = 0;
        long realAmountStoreTotal = 0;

        if (null != resultBeans &&  0 != resultBeans.size())
        {
            writeGroupExcelData(sheet, styles, resultBeans);
        }


        int colCount = 0;
        //2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題                
        //titles.length == 欄位數
      
        while (colCount <= titles.length)
        {
            sheet.autoSizeColumn((short) colCount++,true);//自動調整欄位
        }
      //2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        // Write the output to a file
        String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
        File directory = new File(tempDownloadFilePath);             
        if(!directory.exists())
        {
            try {
                FileUtils.forceMkdir(directory);
            }
            catch (IOException e) {
                e.printStackTrace();
            }
        }

        String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-客戶繳款記錄查詢.xls";

        FileOutputStream out;
        try {
            out = new FileOutputStream(fileFullName);
            wb.write(out);
            out.close();
        }
        catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return fileFullName;
    }
    
    private void writeGroupExcelData(Sheet sheet, Map<String, CellStyle> styles, List resultBeans )
    {
        if (null != resultBeans && 0 != resultBeans.size())
        {
            Row dataRow = null;
            Cell cell = null;
            int rownum = sheet.getLastRowNum() + 1;
            String cellRangeAddress = null;
            for(Iterator r=resultBeans.iterator();r.hasNext();)
            {
            	SoezListBean listBean = (SoezListBean)r.next();
            	
            	dataRow = sheet.createRow(rownum++);
                if (null == cellRangeAddress)
                    cellRangeAddress = "$A$"+rownum;
                dataRow.setHeightInPoints((float) 25);
            	
                cell = dataRow.createCell(0); 
                cell.setCellValue(listBean.getPayment().getVirtualAccount());
                cell.setCellStyle(styles.get("cell"));
                cell = dataRow.createCell(1); 
                cell.setCellValue(listBean.getPayment().getPayerName());
                cell.setCellStyle(styles.get("cell_left"));                
                cell = dataRow.createCell(2); 
                cell.setCellValue(listBean.getPayment().getPayerId());
                cell.setCellStyle(styles.get("cell_left"));
                cell = dataRow.createCell(3); 
                cell.setCellValue(listBean.getAvbal());
                cell.setCellStyle(styles.get("cell_number_total"));
                
            }
         
        }
    }

    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontName("標楷體");
        titleFont.setFontHeightInPoints((short)24);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);

        Font title2Font = wb.createFont();
        title2Font.setFontName("標楷體");
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        title2Font.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(title2Font);      
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styles.put("title2", style);

        Font headerFont = wb.createFont();
        headerFont.setFontName("標楷體");
        headerFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(headerFont);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("header", style);

        Font cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);
              
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell_left", style);

        style = wb.createCellStyle();
        Font cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number", style);

        style = wb.createCellStyle();
        Font cellDateFont = wb.createFont();
        cellDateFont.setFontName("Arial");
        cellDateFont.setFontHeightInPoints((short)12);
        style.setFont(cellDateFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
        styles.put("cell_date", style);


        cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)18);
        cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell_sub_total", style);

        style = wb.createCellStyle();
        cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number_sub_total", style);




        cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)14);
        cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell_total", style);

        style = wb.createCellStyle();
        cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number_total", style);

        return styles;
    }

    

	public GroupDao getGroupDao() {
		return groupDao;
	}

	public void setGroupDao(GroupDao groupDao) {
		this.groupDao = groupDao;
	}

	public PaymentDao getPaymentDao() {
		return paymentDao;
	}

	public void setPaymentDao(PaymentDao paymentDao) {
		this.paymentDao = paymentDao;
	}

	
}
